/*
 * #%L
 * SwiftUICommonUtilities
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
/**
 * 
 */
package com.linkwithweb.products.swiftui.utils;

import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellRangeAddress;

import com.linkwithweb.products.swiftui.context.ExcelFile;
import com.linkwithweb.products.swiftui.context.ExcelTab;
import com.linkwithweb.products.swiftui.context.SystemContext;
import com.linkwithweb.products.swiftui.ui.model.ProcessingInstructions;

/**
 * @author ashwin kumar
 * 
 */
public class GenericExcelReader {

	public static boolean allBlankCells(Iterator cells) {
		boolean result = true;
		while (cells.hasNext() && result == true) {
			HSSFCell cell = (HSSFCell) cells.next();
			if (cell == null) {
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
				try {
					if (cell.getNumericCellValue() <= Double.MAX_VALUE
							|| cell.getNumericCellValue() >= Double.MIN_VALUE) {
						result = false; // the cell is a valid numeric => not a
						// blank row
					}
				} catch (Exception e) {
					// do nothing, the value is not numeric and the error will
					// be catched later
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				try {
					if (cell.getStringCellValue().trim().compareTo("") != 0) {
						result = false; // the cell is a valid string => not a
						// blank row
					}
				} catch (Exception e) {
					// do nothing, the value is not string and the error will be
					// catched later
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
				try {
					if (cell.getBooleanCellValue() || !cell.getBooleanCellValue()) {
						result = false; // the cell is a valid boolean => not a
						// blank row
					}
				} catch (Exception e) {
					// do nothing, the value is not boolean and the error will
					// be catched later
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
				try {
					if (cell.getStringCellValue().trim().compareTo("") != 0) {
						result = false; // the cell is a valid boolean => not a
						// blank row
					}
				} catch (Exception e) {
					// do nothing, the value is not boolean and the error will
					// be catched later
				}
			}

		}
		return result;
	}

	/**
	 * @param fileName
	 * @param headerRowNumber
	 *            TODO
	 * @param sheetNumber
	 *            TODO
	 * @param instructions
	 * @return
	 */
	public static List<Map<String, Object>> readExcelData(String fileName, List<String> columnNames,
			Integer headerRowNumber, Integer sheetNumber, ProcessingInstructions instructions) {

		// TODO Fix java.lang.IndexOutOfBoundsException: Index: 37, Size: 37
		/**
		 * --Define a Vector --Holds Vectors Of Cells
		 */
		List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();

		try {
			/** Creating Input Stream **/
			// InputStream myInput= ReadExcelFile.class.getResourceAsStream(
			// fileName );
			FileInputStream myInput = new FileInputStream(fileName);

			/** Create a POIFSFileSystem object **/
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

			/** Create a workbook using the File System **/
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			/** To Evaluate Formulae **/
			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
			/** Get the first sheet from workbook **/
			HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNumber);
			instructions.setTabName(mySheet.getSheetName());

			/** We now need something to iterate through the cells. **/
			Iterator rowIter = mySheet.rowIterator();

			int rowNumber = 1;
			int urlCount = 0;
			Map<String, Object> rowMap = new HashMap<String, Object>();
			int numRowsEmpty = 0;
			DecimalFormat df = new DecimalFormat("#####################");
			while (rowIter.hasNext()) {
				rowMap = new HashMap<String, Object>();
				HSSFRow myRow = (HSSFRow) rowIter.next();
				short minColIx = myRow.getFirstCellNum();
				short maxColIx = myRow.getLastCellNum();
				if (rowNumber < headerRowNumber) {
					rowNumber = rowNumber + 1;
					continue;
				} else if (rowNumber == headerRowNumber) {
					rowNumber = rowNumber + 1;
					for (short colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell myCell = myRow.getCell(colIx);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							int bracketIndex = columnName.indexOf("(");
							if (bracketIndex != -1) {
								columnName = columnName.substring(0, bracketIndex);
							}
							columnName = columnName.trim();
							columnNames.add(columnName);
						} else {
							columnNames.add("EMPTY COLUMN");
						}

					}
					continue;
				}
				int count = 1;
				for (short colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell myCell = myRow.getCell(colIx);
					if (myCell == null) {
						continue;
					}

					try {

						if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							rowMap.put(columnNames.get(colIx).trim(), myCell.getStringCellValue());
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							try {
								if (columnNames.get(colIx).trim().equals("Student Birthday")) {
									rowMap.put(columnNames.get(colIx).trim(), myCell.getDateCellValue());
								} else {
									rowMap.put(columnNames.get(colIx).trim(), df.format(myCell.getNumericCellValue()));
								}
							} catch (Exception e1) {
								rowMap.put(columnNames.get(colIx).trim(), myCell.getStringCellValue());
							}
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							try {
								if (StringUtils.isNotBlank(evaluator.evaluate(myCell).getStringValue())) {
									rowMap.put(columnNames.get(colIx).trim(), evaluator.evaluate(myCell)
											.getStringValue());
								} else {
									rowMap.put(columnNames.get(colIx).trim(), evaluator.evaluate(myCell)
											.getNumberValue());
								}

							} catch (Exception e1) {
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}

				}
				excelData.add(rowMap);
				rowNumber = rowNumber + 1;
				// excelData.add(context);

				// excelData.addElement(cellStoreVector);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return excelData;
	}

	/**
	 * @param fileName
	 * @param excelContext
	 * @param headerRowNumber
	 * @param sheetNumber
	 * @param instructions
	 * @return
	 */
	public static List<Map<String, Object>> readExcelData(ExcelFile excelContext, Integer headerRowNumber,
			Integer sheetNumber, ProcessingInstructions instructions) {

		// TODO Fix java.lang.IndexOutOfBoundsException: Index: 37, Size: 37
		/**
		 * --Define a Vector --Holds Vectors Of Cells
		 */
		List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();

		try {
			/** Creating Input Stream **/
			// InputStream myInput= ReadExcelFile.class.getResourceAsStream(
			// fileName );
			FileInputStream myInput = new FileInputStream(excelContext.getFilePath());

			/** Create a POIFSFileSystem object **/
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

			/** Create a workbook using the File System **/
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			/** To Evaluate Formulae **/
			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
			/** Get the first sheet from workbook **/
			HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNumber);
			instructions.setTabName(mySheet.getSheetName());

			excelContext.getExcelTabs().add(new ExcelTab());

			/** We now need something to iterate through the cells. **/
			Iterator rowIter = mySheet.rowIterator();

			int rowNumber = 1;
			int urlCount = 0;
			Map<String, Object> rowMap = new HashMap<String, Object>();
			int numRowsEmpty = 0;
			DecimalFormat df = new DecimalFormat("#####################");
			while (rowIter.hasNext()) {
				rowMap = new HashMap<String, Object>();
				HSSFRow myRow = (HSSFRow) rowIter.next();
				short minColIx = myRow.getFirstCellNum();
				short maxColIx = myRow.getLastCellNum();
				if (rowNumber < headerRowNumber) {
					if (rowNumber == 1) {
						excelContext.getExcelTabs().get(sheetNumber).setTableName(
								myRow.getCell(minColIx + 1).getStringCellValue());
					} else if (rowNumber == 2) {
						excelContext.getExcelTabs().get(sheetNumber).setSchemaName(
								myRow.getCell(minColIx + 1).getStringCellValue());
					} else if (rowNumber == 3) {
						excelContext.getExcelTabs().get(sheetNumber).setPrimaryKeyColumn(
								myRow.getCell(minColIx + 1).getStringCellValue());
					} else if (rowNumber == 4) {
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell myCell = myRow.getCell(colIx);
							if (myCell != null) {
								String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t",
										"").replaceAll("\r", "");
								/*
								 * int bracketIndex = columnName.indexOf("(");
								 * if (bracketIndex != -1) { columnName =
								 * columnName.substring(0, bracketIndex); }
								 */
								columnName = columnName.trim();
								excelContext.getExcelTabs().get(sheetNumber).getColumnTypes().add(columnName);
							} else {
								excelContext.getExcelTabs().get(sheetNumber).getColumnTypes().add("varchar(20)");
							}

						}
					}
					rowNumber = rowNumber + 1;
					continue;
				} else if (rowNumber == headerRowNumber) {
					rowNumber = rowNumber + 1;
					for (short colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell myCell = myRow.getCell(colIx);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							int bracketIndex = columnName.indexOf("(");
							if (bracketIndex != -1) {
								columnName = columnName.substring(0, bracketIndex);
							}
							columnName = columnName.trim();
							excelContext.getExcelTabs().get(sheetNumber).getColumnNames().add(columnName);
						} else {
							excelContext.getExcelTabs().get(sheetNumber).getColumnNames().add("EMPTY COLUMN");
						}

					}
					continue;
				}
				int count = 1;
				for (short colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell myCell = myRow.getCell(colIx);
					if (myCell == null) {
						continue;
					}

					try {

						if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx).trim(),
									myCell.getStringCellValue());
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							try {
								if (StringUtils.containsIgnoreCase(excelContext.getExcelTabs().get(sheetNumber)
										.getColumnTypes().get(colIx).trim(), "datetime")) {
									rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx)
											.trim(), myCell.getDateCellValue());
								} else {
									rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx)
											.trim(), df.format(myCell.getNumericCellValue()));
								}
							} catch (Exception e1) {
								rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx)
										.trim(), myCell.getStringCellValue());
							}
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							try {
								if (StringUtils.isNotBlank(evaluator.evaluate(myCell).getStringValue())) {
									rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx)
											.trim(), evaluator.evaluate(myCell).getStringValue());
								} else {
									rowMap.put(excelContext.getExcelTabs().get(sheetNumber).getColumnNames().get(colIx)
											.trim(), evaluator.evaluate(myCell).getNumberValue());
								}

							} catch (Exception e1) {
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}

				}
				excelData.add(rowMap);
				rowNumber = rowNumber + 1;
				// excelData.add(context);

				// excelData.addElement(cellStoreVector);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return excelData;
	}

	public static List<Map<String, Object>> readSimpleDataSheet(ExcelFile excelContext, Integer headerRowNumber,
			String sheetName, ProcessingInstructions instructions) {
		/**
		 * --Define a Vector --Holds Vectors Of Cells
		 */
		List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();

		try {
			/** Creating Input Stream **/
			// InputStream myInput= ReadExcelFile.class.getResourceAsStream(
			// fileName );
			FileInputStream myInput = new FileInputStream(excelContext.getFilePath());

			/** Create a POIFSFileSystem object **/
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

			/** Create a workbook using the File System **/
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			/** To Evaluate Formulae **/
			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
			/** Get the first sheet from workbook **/
			HSSFSheet mySheet = myWorkBook.getSheetAt(0);

			instructions.setTabName(mySheet.getSheetName());
			ExcelTab dataTab = new ExcelTab();
			dataTab.setTabType(ExcelTab.TabTypes.DATA_TAB);
			excelContext.getExcelTabs().add(dataTab);

			/** We now need something to iterate through the cells. **/
			Iterator rowIter = mySheet.rowIterator();

			/**
			 * Read Table Name and Primary Keys
			 */
			String tableName = mySheet.getRow(0).getCell(0).getStringCellValue();
			String primaryKeyColumnNames = mySheet.getRow(1).getCell(0).getStringCellValue();
			dataTab.setPrimaryKeyColumn(primaryKeyColumnNames.replaceAll("Primary Keys: ", ""));
			dataTab.setTableName(tableName.replaceAll("Table Name: ", ""));

			/**
			 * Now process rows
			 */
			int rowNumber = 0;
			Map<String, Object> rowMap = new HashMap<String, Object>();
			DecimalFormat df = new DecimalFormat("#####################.########");
			while (rowIter.hasNext()) {
				rowMap = new HashMap<String, Object>();
				HSSFRow myRow = (HSSFRow) rowIter.next();
				short minColIx = myRow.getFirstCellNum();

				/**
				 * Start from second column as first column dont have data
				 */
				minColIx = 1;

				short maxColIx = myRow.getLastCellNum();
				if (rowNumber < headerRowNumber) {
					if (rowNumber == headerRowNumber - 2) {
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell myCell = myRow.getCell(colIx);
							if (myCell != null) {
								String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t",
										"").replaceAll("\r", "");
								columnName = columnName.trim();
								dataTab.getColumnConstrints().add(columnName);
							} else {
								dataTab.getColumnConstrints().add("EMPTY COLUMN");
							}

						}
					} else if (rowNumber == headerRowNumber - 1) {
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell myCell = myRow.getCell(colIx);
							if (myCell != null) {
								String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t",
										"").replaceAll("\r", "");
								columnName = columnName.trim();
								dataTab.getColumnTypes().add(columnName);
							} else {
								dataTab.getColumnTypes().add("EMPTY COLUMN");
							}

						}
					}
					rowNumber = rowNumber + 1;
					continue;
				} else if (rowNumber == headerRowNumber) {
					rowNumber = rowNumber + 1;
					for (short colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell myCell = myRow.getCell(colIx);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							columnName = columnName.trim();

							if (StringUtils.isBlank(columnName)) {
								dataTab.getColumnNames().add("EMPTY COLUMN");
							} else {
								dataTab.getColumnNames().add(columnName);
							}
						} else {
							dataTab.getColumnNames().add("EMPTY COLUMN");
						}

					}
					continue;
				}
				int count = 1;
				for (short colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell myCell = myRow.getCell(colIx);
					if (myCell == null) {
						continue;
					}

					try {

						if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "").replaceAll(
									"\t", "").replaceAll("\r", ""), myCell.getStringCellValue());
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							try {
								if (HSSFDateUtil.isCellDateFormatted(myCell)) {
									if (HSSFDateUtil.isValidExcelDate(myCell.getNumericCellValue())) {
										rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
												.replaceAll("\t", "").replaceAll("\r", ""), myCell.getDateCellValue());
									} else {
										rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
												.replaceAll("\t", "").replaceAll("\r", ""), df.format(myCell
												.getNumericCellValue()));
									}
								} else {
									rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
											.replaceAll("\t", "").replaceAll("\r", ""), df.format(myCell
											.getNumericCellValue()));
								}

							} catch (Exception e1) {
								rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
										.replaceAll("\t", "").replaceAll("\r", ""), myCell.getNumericCellValue());
							}
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							try {
								if (StringUtils.isNotBlank(evaluator.evaluate(myCell).getStringValue())) {
									rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
											.replaceAll("\t", "").replaceAll("\r", ""), evaluator.evaluate(myCell)
											.getStringValue());
								} else {
									rowMap.put(dataTab.getColumnNames().get(colIx - 1).trim().replaceAll("\n", "")
											.replaceAll("\t", "").replaceAll("\r", ""), evaluator.evaluate(myCell)
											.getNumberValue());
								}

							} catch (Exception e1) {
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}

				}
				/**
				 * Add Type of action for this record...
				 */
				String action = "Add";
				if (StringUtils.isNotBlank(myRow.getCell(0).getStringCellValue())) {
					action = myRow.getCell(0).getStringCellValue();
				}
				rowMap.put("FRM_UPLOAD_ACTION", action);

				excelData.add(rowMap);
				rowNumber = rowNumber + 1;
			}// End of iteration over all rows..
		} catch (Exception e) {
			e.printStackTrace();
		}

		return excelData;
	}

	/**
	 * @param excelContext
	 * @param headerRowNumber
	 * @param sheetName
	 * @param instructions
	 * @return
	 */
	public static List<Map<String, Object>> readComplexDataSheet(ExcelFile excelContext, Integer headerRowNumber,
			String sheetName, ProcessingInstructions instructions) {
		/**
		 * --Define a Vector --Holds Vectors Of Cells
		 */
		List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();

		try {
			/** Creating Input Stream **/
			// InputStream myInput= ReadExcelFile.class.getResourceAsStream(
			// fileName );
			FileInputStream myInput = new FileInputStream(excelContext.getFilePath());

			/** Create a POIFSFileSystem object **/
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

			/** Create a workbook using the File System **/
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			/** To Evaluate Formulae **/

			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();

			/** Get the first sheet from workbook **/
			HSSFSheet mySheet = myWorkBook.getSheetAt(0);

			instructions.setTabName(mySheet.getSheetName());

			/**
			 * if no sheet found
			 */
			if (mySheet == null) {
				return null;
			}
			

			ExcelTab dataTab = new ExcelTab();
			dataTab.setTabType(ExcelTab.TabTypes.DATA_TAB);
			dataTab.setTableName(sheetName.replaceAll(" ", "_").replaceAll("-", "_"));
			excelContext.getExcelTabs().add(dataTab);

			/** We now need something to iterate through the cells. **/
			Iterator rowIter = mySheet.rowIterator();

			/**
			 * To handle Merged Cells
			 */
			List<CellRangeAddress> regionsList = new ArrayList<CellRangeAddress>();
			for (int i = 0; i < mySheet.getNumMergedRegions(); i++) {
				regionsList.add(mySheet.getMergedRegion(i));
			}

			int rowNumber = 1;
			Map<String, Object> rowMap = new HashMap<String, Object>();
			DecimalFormat df = new DecimalFormat("#####################.##");
			while (rowIter.hasNext()) {
				rowMap = new HashMap<String, Object>();
				HSSFRow myRow = (HSSFRow) rowIter.next();
				short minColIx = myRow.getFirstCellNum();
				short maxColIx = myRow.getLastCellNum();
				if (rowNumber < headerRowNumber) {
					if (rowNumber == headerRowNumber - 1) {
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell myCell = myRow.getCell(colIx);
							if (myCell != null) {

								/**
								 * If cell is merged cell then assign the first
								 * cell to this..
								 */
								for (CellRangeAddress region : regionsList) {
									// If the region does contain the cell you
									// have just read from the row
									if (region.isInRange(myCell.getRowIndex(), myCell.getColumnIndex())) {
										// Now, you need to get the cell from
										// the top left hand corner of this
										int rowNum = region.getFirstRow();
										int colIndex = region.getFirstColumn();
										myCell = myRow.getCell(colIndex);
										// System.out.println("Cel is in merged region. The value stored in that	region is "
										// + cell.getStringCellValue());
									}
								}

								String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t",
										"").replaceAll("\r", "");
								columnName = columnName.trim();
								dataTab.getColumnGroupNames().add(columnName);
							} else {
								dataTab.getColumnGroupNames().add("-");
							}

						}
					}
					rowNumber = rowNumber + 1;
					continue;
				} else if (rowNumber == headerRowNumber) {
					rowNumber = rowNumber + 1;
					for (short colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell myCell = myRow.getCell(colIx);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							columnName = columnName.trim();

							if (StringUtils.isBlank(columnName)) {
								dataTab.getColumnNames().add("EMPTY COLUMN");
							} else {
								dataTab.getColumnNames().add(columnName);
							}
						} else {
							dataTab.getColumnNames().add("EMPTY COLUMN");
						}

					}
					continue;
				} else if (rowNumber == headerRowNumber + 1) {
					rowNumber = rowNumber + 1;
					for (short colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell myCell = myRow.getCell(colIx);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							columnName = columnName.trim();
							dataTab.getColumnTypes().add(columnName);
						} else {
							dataTab.getColumnTypes().add("EMPTY COLUMN");
						}

					}
					continue;
				}
				int count = 1;
				for (short colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell myCell = myRow.getCell(colIx);
					if (myCell == null) {
						continue;
					}

					try {

						if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							rowMap.put(dataTab.getColumnNames().get(colIx).trim(), myCell.getStringCellValue());
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							try {
								if (HSSFDateUtil.isCellDateFormatted(myCell)) {
									if (HSSFDateUtil.isValidExcelDate(myCell.getNumericCellValue())) {
										rowMap.put(dataTab.getColumnNames().get(colIx).trim().replaceAll("\n", "")
												.replaceAll("\t", "").replaceAll("\r", ""), myCell.getDateCellValue());
									} else {
										rowMap.put(dataTab.getColumnNames().get(colIx).trim().replaceAll("\n", "")
												.replaceAll("\t", "").replaceAll("\r", ""), df.format(myCell
												.getNumericCellValue()));
									}
								} else {
									rowMap.put(dataTab.getColumnNames().get(colIx).trim().replaceAll("\n", "")
											.replaceAll("\t", "").replaceAll("\r", ""), df.format(myCell
											.getNumericCellValue()));
								}

							} catch (Exception e1) {
								rowMap.put(dataTab.getColumnNames().get(colIx).trim().replaceAll("\n", "").replaceAll(
										"\t", "").replaceAll("\r", ""), myCell.getNumericCellValue());
							}
						} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							try {
								if (StringUtils.isNotBlank(evaluator.evaluate(myCell).getStringValue())) {
									rowMap.put(dataTab.getColumnNames().get(colIx).trim(), evaluator.evaluate(myCell)
											.getStringValue());
								} else {
									rowMap.put(dataTab.getColumnNames().get(colIx).trim(), evaluator.evaluate(myCell)
											.getNumberValue());
								}

							} catch (Exception e1) {
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}

				}
				excelData.add(rowMap);
				rowNumber = rowNumber + 1;
			}// End of iteration over all rows..
		} catch (Exception e) {
			e.printStackTrace();
		}

		return excelData;
	}

	/**
	 * @param excelContext
	 * @param headerRowNumber
	 * @param sheetName
	 * @param instructions
	 */
	public static void readLovSheet(ExcelFile excelContext, Integer headerRowNumber, String sheetName,
			ProcessingInstructions instructions) {
		/**
		 * --Define a Vector --Holds Vectors Of Cells
		 */
		List<String> lovColumnNames = new ArrayList<String>();

		try {
			/** Creating Input Stream **/
			// InputStream myInput= ReadExcelFile.class.getResourceAsStream(
			// fileName );
			FileInputStream myInput = new FileInputStream(excelContext.getFilePath());

			/** Create a POIFSFileSystem object **/
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

			/** Create a workbook using the File System **/
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			/** To Evaluate Formulae **/
			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
			/** Get the first sheet from workbook **/
			HSSFSheet mySheet = myWorkBook.getSheetAt(0);

			instructions.setTabName(mySheet.getSheetName());
			ExcelTab lovTab = new ExcelTab();
			lovTab.setTabType(ExcelTab.TabTypes.LOV_TAB);
			excelContext.getExcelTabs().add(lovTab);
			/** We now need something to iterate through the cells. **/
			Iterator rowIter = mySheet.rowIterator();
			int rowNumber = 1;
			DecimalFormat df = new DecimalFormat("#####################");
			int firstCellNum = 0;
			int lastCellNum = 0;
			while (rowIter.hasNext()) {
				HSSFRow myRow = (HSSFRow) rowIter.next();
				short minColIx = myRow.getFirstCellNum();
				firstCellNum = minColIx;
				short maxColIx = myRow.getLastCellNum();
				lastCellNum = maxColIx;
				break;
			}// End of row iteration...

			/**
			 * Now iterate over each Lov column and store values for each row...
			 */
			for (int index = firstCellNum; index <= lastCellNum; index++) {

				String lovName = "";
				/**
				 * Iterate for each column
				 */
				rowNumber = 0;
				rowIter = mySheet.rowIterator();
				while (rowIter.hasNext()) {
					HSSFRow myRow = (HSSFRow) rowIter.next();
					// Skip first row as it is
					if (rowNumber == 0) {
						HSSFCell myCell = myRow.getCell(index);
						if (myCell != null) {
							String columnName = myCell.getStringCellValue().replaceAll("\n", "").replaceAll("\t", "")
									.replaceAll("\r", "");
							lovName = columnName.trim();
							lovTab.getLovValuesMapping().put(lovName, new ArrayList<String>());
						}
						rowNumber = rowNumber + 1;
						continue;
					}

					HSSFCell myCell = myRow.getCell(index);
					if (myCell != null) {
						try {
							if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
								lovTab.getLovValuesMapping().get(lovName).add(myCell.getStringCellValue());
							} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							} else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							}
						} catch (Exception e) {
							e.printStackTrace();
						}

					}

					rowNumber = rowNumber + 1;
				}// End of row iteration...
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ExcelFile excelContext = new ExcelFile();
		Integer headerRowNumber = 5;
		String sheetName = "";
		ProcessingInstructions instructions = new ProcessingInstructions();
		excelContext
				.setFilePath("C:/development/linkwithweb/product/SwiftUI/docs/fwdfwtemplatesfornewtables/PFT_TAXES_Upload.xls");

		readSimpleDataSheet(excelContext, headerRowNumber, "", instructions);
	}

}
